﻿using XChipTool.Enties;
using SqlSugar;
using System;
using System.Collections.Generic;
public class parameter_exportManager : DbContext<parameter_export>
{
 
    //当前类已经继承了 DbContext增、删、查、改的方法

    //这里面写的代码不会给覆盖,如果要重新生成请删除 parameter_exportManager.cs


    #region 教学方法
    /// <summary>
    /// 如果DbContext中的增删查改方法满足不了你，你可以看下具体用法
    /// </summary>
    public void Study()
    {
	     
	   /*********查询*********/

        var data1 = parameter_exportDb.GetById(1);//根据ID查询
        var data2 = parameter_exportDb.GetList();//查询所有
        var data3 = parameter_exportDb.GetList(it => 1 == 1);  //根据条件查询  
        //var data4 = parameter_exportDb.GetSingle(it => 1 == 1);//根据条件查询一条,如果超过一条会报错

        var p = new PageModel() { PageIndex = 1, PageSize = 2 };// 分页查询
        var data5 = parameter_exportDb.GetPageList(it => 1 == 1, p);
        Console.Write(p.PageCount);//返回总数

        var data6 = parameter_exportDb.GetPageList(it => 1 == 1, p, it => SqlFunc.GetRandom(), OrderByType.Asc);// 分页查询加排序
        Console.Write(p.PageCount);//返回总数
     
        List<IConditionalModel> conModels = new List<IConditionalModel>(); //组装条件查询作为条件实现 分页查询加排序
        conModels.Add(new ConditionalModel() { FieldName = typeof(parameter_export).GetProperties()[0].Name, ConditionalType = ConditionalType.Equal, FieldValue = "1" });//id=1
        var data7 = parameter_exportDb.GetPageList(conModels, p, it => SqlFunc.GetRandom(), OrderByType.Asc);

        parameter_exportDb.AsQueryable().Where(x => 1 == 1).ToList();//支持了转换成queryable,我们可以用queryable实现复杂功能

        //我要用事务
        var result = Db.Ado.UseTran(() =>
         {
            //写事务代码
        });
        if (result.IsSuccess)
        {
            //事务成功
        }

        //多表查询地址 http://www.codeisbug.com/Doc/8/1124



        /*********插入*********/
        var insertData = new parameter_export() { };//测试参数
        var insertArray = new parameter_export[] { insertData };
        parameter_exportDb.Insert(insertData);//插入
        parameter_exportDb.InsertRange(insertArray);//批量插入
        var id = parameter_exportDb.InsertReturnIdentity(insertData);//插入返回自增列
        parameter_exportDb.AsInsertable(insertData).ExecuteCommand();//我们可以转成 Insertable实现复杂插入



		/*********更新*********/
	    var updateData = new parameter_export() {  };//测试参数
        var updateArray = new parameter_export[] { updateData };//测试参数
        parameter_exportDb.Update(updateData);//根据实体更新
        parameter_exportDb.UpdateRange(updateArray);//批量更新
        //parameter_exportDb.Update(it => new parameter_export() { Name = "a", CreateTime = DateTime.Now }, it => it.id==1);// 只更新Name列和CreateTime列，其它列不更新，条件id=1
        parameter_exportDb.AsUpdateable(updateData).ExecuteCommand();



		/*********删除*********/
	    var deldata = new parameter_export() {  };//测试参数
        parameter_exportDb.Delete(deldata);//根据实体删除
        parameter_exportDb.DeleteById(1);//根据主键删除
        parameter_exportDb.DeleteById(new int[] { 1,2});//根据主键数组删除
        parameter_exportDb.Delete(it=>1==2);//根据条件删除
        parameter_exportDb.AsDeleteable().Where(it=>1==2).ExecuteCommand();//转成Deleteable实现复杂的操作
    }
    #endregion


    /// <summary>
    /// 返回所有
    /// </summary>
    /// <returns></returns>
    public List<parameter_export_expand> GetListToExport()
    {
        return Db.Queryable<parameter_export, parameter_export_patch>((pa, pb) => new object[] {
        JoinType.Left,pa.ReadAddr==pb.ReadAddr}).Select((pa, pb) => new parameter_export_expand
        {
            id = pa.id,
            addr = pa.addr,
            ReadAddr = pa.ReadAddr,
            RearNote = pa.RearNote,
            PreNote = pa.PreNote,
            Module = pa.Module,
            RelationAddr = pb.RelationAddr,
            ConditionAddr = pb.ConditionAddr,
            Bit = pb.Bit,
            Val = pb.Val,
            SortNum = pa.SortNum,
        }).ToList();
    }
    /// <summary>
    /// 返回更具场景进行筛选后的已排序
    /// </summary>
    /// <returns></returns>
    public List<parameter_export_expand> GetListToExport(string sceneStr)
    {
       return Db.Ado.SqlQuery<parameter_export_expand>(@"SELECT
	pe.id,
	pe.addr,
	pe.ReadAddr,
	pe.RearNote,
	pe.PreNote,
	pe.Module,
    pes.Name PidM,
	pep.RelationAddr,
	pep.ConditionAddr,
	pep.Bit,
  pep.Val,
	pe.SortNum 
FROM
	parameter_export_scene AS pes
	
	LEFT JOIN parameter_export AS pe ON pes.Module = pe.Module
	LEFT JOIN parameter_export_patch AS pep ON pe.ReadAddr == pep.ReadAddr 
WHERE
	pes.pid = (SELECT id FROM parameter_export_scene WHERE Name = @name AND pid = 0)
ORDER BY
	pes.Sort ASC,
	pe.SortNum ASC;", new  { name = sceneStr});        
    }
}
